﻿namespace DatabaseDoc.Connectors.Sql
{
    using System;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.IO;
    using System.Linq;
    using System.Text;
    using System.Text.RegularExpressions;

    using DatabaseDoc.Library;

    using Microsoft.SqlServer.Management.Common;
    using Microsoft.SqlServer.Management.Sdk.Sfc;
    using Microsoft.SqlServer.Management.Smo;

    using Column = DatabaseDoc.Library.Column;
    using Database = DatabaseDoc.Library.Database;
    using Server = DatabaseDoc.Library.Server;
    using Table = DatabaseDoc.Library.Table;

    public class SqlDatabaseConnector : IDatabaseConnector
    {
        public string Key
        {
            get
            {
                return "MSSQL";
            }
        }

        public DatabaseDoc Populate(string[] serverNames, string databaseName, string tableName)
        {
            if (serverNames == null || serverNames.Length == 0)
            {
                return null;
            }

            var databaseDoc = new DatabaseDoc(this.Key) { Servers = new List<Server>(serverNames.Length) };

            foreach (var serverName in serverNames)
            {
                var server = GetServer(serverName, databaseName, tableName);
                databaseDoc.Servers.Add(server);
            }

            return databaseDoc;
        }

        public bool DatabaseExists(string serverName, string databaseName)
        {
            using (var serverConnection = new SqlServerConnection(serverName))
            {
                var databaseExists = serverConnection.Current.Databases.Contains(databaseName);
                return databaseExists;
            }
        }

        public void CreateDatabase(string serverName, string databaseName)
        {
            if (this.DatabaseExists(serverName, databaseName))
            {
                throw new ConnectorException(string.Format("Database {0} already exists on server {1}", databaseName, serverName));
            }

            using (var serverConnection = new SqlServerConnection(serverName))
            {
                var database = new Microsoft.SqlServer.Management.Smo.Database(serverConnection.Current, databaseName);
                //database.Roles.Add(new DatabaseRole());
                database.Create();
            }
        }

        public void DropDatabase(string serverName, string databaseName)
        {
            if (!this.DatabaseExists(serverName, databaseName))
            {
                throw new ConnectorException(string.Format("Trying to drop a database {0} on server {1} which doesn't exist", databaseName, serverName));
            }

            using (var serverConnection = new SqlServerConnection(serverName))
            {
                // See http://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.server.killdatabase.aspx
                serverConnection.Current.KillAllProcesses(databaseName);
                serverConnection.Current.KillDatabase(databaseName);

                //var database = new Microsoft.SqlServer.Management.Smo.Database(serverConnection.Current, databaseName);

                // Refresh is required in order to drop the database again later
                // See http://www.daniweb.com/software-development/csharp/threads/93529/c-failing-to-drop-stored-procedure-from-sql-server-using-smo
                //database.Refresh();
                //database.Drop();
            }
        }

        /// <summary>
        /// SMO causes exception when running with the .NET 4.0 framework.
        /// So we must manually split any scripts on the GO command
        /// See http://stackoverflow.com/questions/650098/how-to-execute-an-sql-script-file-using-c-sharp       
        /// </summary>
        /// <param name="serverName">Name of the server.</param>
        /// <param name="databaseName">Name of the database.</param>
        /// <param name="scripts">The collection of scripts.</param>
        public void RunScripts(string serverName, string databaseName, IEnumerable<string> scripts)
        {
            if (scripts == null || scripts.Count() == 0)
            {
                return;
            }

            using (var serverConnection = new SqlServerConnection(serverName, databaseName))
            {
                using (var connection = new SqlConnection(serverConnection.Current.ConnectionContext.ConnectionString))
                {
                    connection.Open();
                    using (var transaction = connection.BeginTransaction())
                    {
                        foreach (var script in scripts)
                        {
                            // Split script on GO command
                            var commandStrings = Regex.Split(
                                script, "^\\s*GO\\s*$", RegexOptions.Multiline | RegexOptions.IgnoreCase);

                            foreach (var commandString in commandStrings)
                            {
                                if (!string.IsNullOrEmpty(commandString.Trim()))
                                {
                                    new SqlCommand(commandString, connection, transaction).ExecuteNonQuery();
                                }
                            }
                        }

                        transaction.Commit();
                    }
                }
            }
        }

        public void GenerateScripts(string serverName, string databaseName, string tableName, string scriptPrefix)
        {
            using (var serverConnection = new SqlServerConnection(serverName, databaseName))
            {                
                // scripter.ScriptingProgress += new ProgressReportEventHandler(ScriptingProgressEventHandler);                
                this.GenerateTablesScript(serverConnection, databaseName, scriptPrefix);                
                this.GenerateIndexesScript(serverConnection, databaseName, scriptPrefix);
                this.GenerateForeignKeysScript(serverConnection, databaseName, scriptPrefix);
                this.GenerateProgrammabilityScript(serverConnection, databaseName, scriptPrefix);
            }
        }      

        private void GenerateTablesScript(SqlServerConnection serverConnection, string databaseName, string scriptPrefix)
        {            
            var database = serverConnection.Current.Databases[databaseName];

            if (database == null)
            {
                throw new ConnectionException(string.Format("Database {0} does not exist on server {1}", databaseName, serverConnection.Current.Name));
            }

            var scripter = new Scripter(serverConnection.Current)
                {
                    Options = ScriptingOptionsFactory.GetTableScriptingOptions(SqlServerVersion.Version90)
                };

            var stringBuilder = new StringBuilder();

            // Tables            
            foreach (Microsoft.SqlServer.Management.Smo.Table table in database.Tables)
            {
                if (!table.IsSystemObject)
                {
                    stringBuilder.Append(this.ScriptObject(new[] { table.Urn }, scripter));
                }
            }

            // Views            
            foreach (View view in database.Views)
            {
                if (!view.IsSystemObject)
                {
                    stringBuilder.Append(this.ScriptObject(new[] { view.Urn }, scripter));
                }
            }
          
            // TODO: make these options configurable            
            this.WriteScript(stringBuilder.ToString(), string.Format("1-{0}-tables", scriptPrefix));
        }      

        private void GenerateIndexesScript(SqlServerConnection serverConnection, string databaseName, string scriptPrefix)
        {
            var database = serverConnection.Current.Databases[databaseName];

            if (database == null)
            {
                throw new ConnectionException(string.Format("Database {0} does not exist on server {1}", databaseName, serverConnection.Current.Name));
            }

            var scripter = new Scripter(serverConnection.Current)
            {
                Options = ScriptingOptionsFactory.GetIndexesScriptingOptions(SqlServerVersion.Version90)
            };

            var stringBuilder = new StringBuilder();

            // Indexes            
            foreach (Microsoft.SqlServer.Management.Smo.Table table in database.Tables)
            {
                foreach (Index index in table.Indexes)
                {
                    stringBuilder.Append(this.ScriptObject(new[] { index.Urn }, scripter));                    
                }                                       
            }
            
            this.WriteScript(stringBuilder.ToString(), string.Format("2-{0}-indexes", scriptPrefix));
        }

        private void GenerateForeignKeysScript(SqlServerConnection serverConnection, string databaseName, string scriptPrefix)
        {
            var database = serverConnection.Current.Databases[databaseName];

            if (database == null)
            {
                throw new ConnectionException(string.Format("Database {0} does not exist on server {1}", databaseName, serverConnection.Current.Name));
            }

            var scripter = new Scripter(serverConnection.Current)
            {
                Options = ScriptingOptionsFactory.GetForeignKeysScriptingOptions(SqlServerVersion.Version90)
            };

            var stringBuilder = new StringBuilder();

            // Foreign Keys            
            foreach (Microsoft.SqlServer.Management.Smo.Table table in database.Tables)
            {
                foreach (ForeignKey foreignKey in table.ForeignKeys)
                {
                    stringBuilder.Append(this.ScriptObject(new[] { foreignKey.Urn }, scripter));
                }
            }

            this.WriteScript(stringBuilder.ToString(), string.Format("3-{0}-keys", scriptPrefix));
        }

        private void GenerateProgrammabilityScript(SqlServerConnection serverConnection, string databaseName, string scriptPrefix)
        {
            var database = serverConnection.Current.Databases[databaseName];

            if (database == null)
            {
                throw new ConnectionException(string.Format("Database {0} does not exist on server {1}", databaseName, serverConnection.Current.Name));
            }

            var scripter = new Scripter(serverConnection.Current)
            {
                Options = ScriptingOptionsFactory.GetProgrammabilityScriptingOptions(SqlServerVersion.Version90)
            };

            var stringBuilder = new StringBuilder();

            // User Defined Functions            
            foreach (UserDefinedFunction udf in database.UserDefinedFunctions)
            {
                if (!udf.IsSystemObject)
                {
                    stringBuilder.Append(this.ScriptObject(new[] { udf.Urn }, scripter));
                }
            }

            // Stored Procedures            
            foreach (StoredProcedure storedProcedure in database.StoredProcedures)
            {
                if (!storedProcedure.IsSystemObject)
                {
                    stringBuilder.Append(this.ScriptObject(new[] { storedProcedure.Urn }, scripter));
                }
            }

            // TODO: make these options configurable            
            this.WriteScript(stringBuilder.ToString(), string.Format("4-{0}-programmability", scriptPrefix));
        }


        private void WriteScript(string script, string scriptPrefix)
        {            
            //if (chkScriptDatabase.Checked)
            //{
            //  sbScript.Append(ScriptObject(new Urn[] { db.Urn }, scripter));
            //}              
            
            var fileName = string.Format("{0}.sql", scriptPrefix);
            File.WriteAllText(fileName, script);



            //if (!string.IsNullOrWhiteSpace(tableName))
            //{
            //    script = this.ScriptTable(serverConnection, scripter, databaseName, tableName);
            //}
            //else if (!string.IsNullOrWhiteSpace(databaseName))
            //{
            //    script = this.ScriptDatabaseTables(serverConnection, scripter, databaseName);
            //}
            //else
            //{
            //    script = this.ScriptServer(serverConnection, scripter);
            //}

            
        }

        private string ScriptObject(Urn[] urns, Scripter scripter)
        {
            var stringCollection = scripter.Script(urns);
            var stringBuilder = new StringBuilder();

            foreach (var str in stringCollection)
            {
                stringBuilder.AppendFormat("{0}{1}GO{2}{3}", str, Environment.NewLine, Environment.NewLine, Environment.NewLine);
            }

            return stringBuilder.ToString();
        }

        private string ScriptServer(SqlServerConnection serverConnection, Scripter scripter)
        {
            throw new NotImplementedException();
        }


        private string ScriptTable(SqlServerConnection serverConnection, Scripter scripter, string databaseName, string tableName)
        {
            throw new NotImplementedException();
        }

        private static Server GetServer(string serverName, string databaseName, string tableName)
        {
            var server = new Server { Name = serverName, Databases = new List<Database>() };

            using (var serverConnection = new SqlServerConnection(serverName, databaseName))
            {
                foreach (Microsoft.SqlServer.Management.Smo.Database database in serverConnection.Current.Databases)
                {
                    if (database.IsSystemObject)
                    {
                        continue;
                    }

                    if (database.Status != DatabaseStatus.Normal)
                    {
                        continue;
                    }

                    if (!string.IsNullOrEmpty(databaseName) && string.Compare(database.Name, databaseName, true) != 0)
                    {
                        continue;
                    }

                    var db = GetDatabase(database, tableName);
                    server.Databases.Add(db);
                }
            }

            return server;
        }

        private static Database GetDatabase(Microsoft.SqlServer.Management.Smo.Database database, string tableName)
        {
            var db = new Database { Name = database.Name, Tables = new List<Table>() };

            foreach (Microsoft.SqlServer.Management.Smo.Table table in database.Tables)
            {
                if (!string.IsNullOrEmpty(tableName) && string.Compare(table.Name, tableName, true) != 0)
                {
                    continue;
                }

                var tb = new Table { Name = table.Name, Columns = new List<Column>() };

                foreach (Microsoft.SqlServer.Management.Smo.Column column in table.Columns)
                {
                    var col = new Column
                        {
                            InPrimaryKey = column.InPrimaryKey,
                            Name = column.Name,
                            DataType = column.DataType.SqlDataType.ToString(),
                            DataTypeSize = column.DataType.MaximumLength,
                            Nullable = column.Nullable
                        };

                    tb.Columns.Add(col);
                }

                db.Tables.Add(tb);
            }

            return db;
        }
    }
}